"""
数据库连接和模型定义
"""

from sqlalchemy import create_engine, Column, Integer, String, Text, DateTime, Boolean, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, Session, relationship
from sqlalchemy.sql import func
from datetime import datetime
from config import get_settings

settings = get_settings()

# 创建数据库引擎
engine = create_engine(
    settings.database_url,
    echo=settings.debug,
    pool_pre_ping=True,
    pool_recycle=300
)

# 创建会话工厂
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# 创建基础模型类
Base = declarative_base()


class User(Base):
    """用户模型"""
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True, index=True, comment="用户ID")
    username = Column(String(50), unique=True, index=True, nullable=False, comment="用户名")
    password = Column(String(255), nullable=False, comment="密码(加密)")
    name = Column(String(100), nullable=False, comment="真实姓名")
    email = Column(String(100), comment="邮箱")
    phone = Column(String(20), comment="手机号")
    avatar = Column(String(255), comment="头像URL")
    department = Column(String(100), comment="部门")
    position = Column(String(100), comment="职位")
    status = Column(Boolean, default=True, comment="状态(True:正常 False:禁用)")
    created_at = Column(DateTime, default=func.now(), comment="创建时间")
    updated_at = Column(DateTime, default=func.now(), onupdate=func.now(), comment="更新时间")


class Announcement(Base):
    """公告模型"""
    __tablename__ = "announcements"
    
    id = Column(Integer, primary_key=True, index=True, comment="公告ID")
    title = Column(String(200), nullable=False, comment="标题")
    content = Column(Text, comment="内容")
    type = Column(String(50), default="general", comment="类型")
    priority = Column(String(20), default="medium", comment="优先级")
    author_id = Column(Integer, ForeignKey("users.id"), comment="作者ID")
    status = Column(String(20), default="published", comment="状态")
    publish_time = Column(DateTime, default=func.now(), comment="发布时间")
    created_at = Column(DateTime, default=func.now(), comment="创建时间")
    updated_at = Column(DateTime, default=func.now(), onupdate=func.now(), comment="更新时间")
    
    # 关联关系
    author = relationship("User", backref="announcements")


class QRCode(Base):
    """二维码模型"""
    __tablename__ = "qr_codes"
    
    id = Column(Integer, primary_key=True, index=True, comment="二维码ID")
    code = Column(String(100), unique=True, index=True, nullable=False, comment="二维码内容")
    type = Column(String(50), nullable=False, comment="类型")
    name = Column(String(200), comment="名称")
    description = Column(Text, comment="描述")
    data = Column(Text, comment="关联数据(JSON格式)")
    status = Column(String(20), default="active", comment="状态")
    created_by = Column(Integer, ForeignKey("users.id"), comment="创建者ID")
    created_at = Column(DateTime, default=func.now(), comment="创建时间")
    updated_at = Column(DateTime, default=func.now(), onupdate=func.now(), comment="更新时间")
    
    # 关联关系
    creator = relationship("User", backref="qr_codes")


class RefreshToken(Base):
    """刷新令牌模型"""
    __tablename__ = "refresh_tokens"
    
    id = Column(Integer, primary_key=True, index=True, comment="令牌ID")
    token = Column(String(255), unique=True, index=True, nullable=False, comment="令牌值")
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False, comment="用户ID")
    expires_at = Column(DateTime, nullable=False, comment="过期时间")
    is_revoked = Column(Boolean, default=False, comment="是否已撤销")
    created_at = Column(DateTime, default=func.now(), comment="创建时间")
    
    # 关联关系
    user = relationship("User", backref="refresh_tokens")


class Activity(Base):
    """活动记录模型"""
    __tablename__ = "activities"
    
    id = Column(Integer, primary_key=True, index=True, comment="活动ID")
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False, comment="用户ID")
    type = Column(String(50), nullable=False, comment="活动类型")
    title = Column(String(200), nullable=False, comment="标题")
    description = Column(Text, comment="描述")
    status = Column(String(50), default="pending", comment="状态")
    data = Column(Text, comment="相关数据(JSON格式)")
    created_at = Column(DateTime, default=func.now(), comment="创建时间")
    
    # 关联关系
    user = relationship("User", backref="activities")


def get_db() -> Session:
    """获取数据库会话"""
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


def create_tables():
    """创建数据库表"""
    Base.metadata.create_all(bind=engine)


def init_sample_data():
    """初始化示例数据"""
    from sqlalchemy.orm import Session
    import hashlib
    import json
    
    db = SessionLocal()
    
    try:
        # 检查是否已有数据
        if db.query(User).first():
            return
        
        # 创建示例用户
        users_data = [
            {
                "username": "admin",
                "password": hashlib.sha256("admin123".encode()).hexdigest(),
                "name": "管理员",
                "email": "admin@company.com",
                "phone": "13800138000",
                "department": "信息技术部",
                "position": "系统管理员"
            },
            {
                "username": "user1",
                "password": hashlib.sha256("user123".encode()).hexdigest(),
                "name": "张三",
                "email": "zhangsan@company.com",
                "phone": "13800138001",
                "department": "销售部",
                "position": "销售经理"
            },
            {
                "username": "user2",
                "password": hashlib.sha256("user123".encode()).hexdigest(),
                "name": "李四",
                "email": "lisi@company.com",
                "phone": "13800138002",
                "department": "技术部",
                "position": "软件工程师"
            }
        ]
        
        for user_data in users_data:
            user = User(**user_data)
            db.add(user)
        
        db.commit()
        
        # 创建示例公告
        admin_user = db.query(User).filter(User.username == "admin").first()
        announcements_data = [
            {
                "title": "系统维护通知",
                "content": "系统将于本周六晚上进行维护升级，预计维护时间2小时",
                "type": "system",
                "priority": "high",
                "author_id": admin_user.id
            },
            {
                "title": "新功能上线",
                "content": "移动端新增扫码功能，支持设备、人员、文档等多种类型二维码扫描",
                "type": "feature",
                "priority": "medium",
                "author_id": admin_user.id
            }
        ]
        
        for announcement_data in announcements_data:
            announcement = Announcement(**announcement_data)
            db.add(announcement)
        
        # 创建示例二维码
        qr_codes_data = [
            {
                "code": "TEST001",
                "type": "equipment",
                "name": "设备001",
                "description": "A栋1楼服务器设备",
                "data": json.dumps({
                    "location": "A栋1楼",
                    "status": "正常",
                    "lastMaintenance": "2024-01-10"
                }),
                "created_by": admin_user.id
            },
            {
                "code": "USER002",
                "type": "user",
                "name": "李四",
                "description": "技术部工程师",
                "data": json.dumps({
                    "department": "技术部",
                    "position": "工程师",
                    "phone": "13800138002"
                }),
                "created_by": admin_user.id
            },
            {
                "code": "DOC003",
                "type": "document",
                "name": "项目计划书",
                "description": "2024年度项目计划",
                "data": json.dumps({
                    "version": "v1.2",
                    "author": "项目组",
                    "updateTime": "2024-01-12"
                }),
                "created_by": admin_user.id
            }
        ]
        
        for qr_data in qr_codes_data:
            qr_code = QRCode(**qr_data)
            db.add(qr_code)
        
        db.commit()
        print("示例数据初始化完成")
        
    except Exception as e:
        print(f"初始化示例数据失败: {e}")
        db.rollback()
    finally:
        db.close()


if __name__ == "__main__":
    # 创建表和初始化数据
    create_tables()
    init_sample_data()